﻿CREATE TRIGGER [dbo].[tr_CarInfo_BookValues]
ON dbo.t_CarInfo_BookValues 
FOR INSERT, UPDATE
AS

IF @@ROWCOUNT = 0 RETURN
IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN

UPDATE t_CarInfo_BookValues SET MileageAddOn = 0
FROM t_CarInfo_BookValues cibv INNER JOIN inserted i ON cibv.VIN = i.VIN
WHERE cibv.Type = 'rough' AND cibv.MileageAddOn <> 0

DELETE t_CarInfo_BookValues 
FROM t_CarInfo_BookValues cibv INNER JOIN inserted i ON cibv.VIN = i.VIN
WHERE cibv.Price = 0

UPDATE CI_BV SET CarBookProvider_ValueTypeID = CBP_VT.ID
FROM t_CarInfo_BookValues AS CI_BV
INNER JOIN inserted i ON CI_BV.VIN = i.VIN AND CI_BV.Type = i.Type
INNER JOIN v_CarBookProvider_ValueType AS CBP_VT ON CI_BV.Type = CBP_VT.TypeName AND CI_BV.Provider = CBP_VT.ProviderCode
WHERE  (CI_BV.CarBookProvider_ValueTypeID IS NULL)

UPDATE C SET DateUpdated = GETDATE()
FROM t_CarInfo_BookValues C INNER JOIN inserted i ON C.VIN = i.VIN AND C.CarBookProvider_ValueTypeID = i.CarBookProvider_ValueTypeID
